2) THE RELATIONAL TABLES IN DETAILS:
geodb_locations:
create table geodb_locations (
loc_id serial primary key,
loc_type integer not null
check (loc_type = CONTINENT or loc_type = STATE or
loc_type = NUTS_I or loc_type = NUTS_II or
loc_type = NUTS_III or loc_type = POL_DIVISION or
loc_type = POPULATED_AREA or loc_type = LOC_AREA_CODE)
);
geodb_locations is the main relation where any location
has to have an entry. It defines an attribute named
loc_id meaning a unique identifier, identifying
this locations in all others tables as well.
The second attribute is named loc_type, and it
should make clear, what kind of entry we can expect here: Are
the geo-coordinates and data etc. related to a town or to an
area code or whatever.
geodb_hierarchies:
create table geodb_hierarchies (
loc_id integer not null references geodb_locations,
level integer not null check (level>0 and level<=9),
id_lvl1 integer not null,
id_lvl2 integer,
id_lvl3 integer,
id_lvl4 integer,
id_lvl5 integer,
id_lvl6 integer,
id_lvl7 integer,
id_lvl8 integer,
id_lvl9 integer,
valid_since date,
date_type_since integer,
valid_until date not null,
date_type_until integer not null
check (
(
(level = 1 and /* loc_id = id_lvl1 and */
id_lvl2 is null and id_lvl3 is null and
id_lvl4 is null and id_lvl5 is null and
id_lvl6 is null and id_lvl7 is null and
id_lvl8 is null and id_lvl9 is null) or
(level = 2 and /* loc_id = id_lvl2 and */
id_lvl1 is not null and id_lvl3 is null and
id_lvl4 is null and id_lvl5 is null and
id_lvl6 is null and id_lvl7 is null and
id_lvl8 is null and id_lvl9 is null) or
(level = 3 and /* loc_id = id_lvl3 and */
id_lvl1 is not null and id_lvl2 is not null and
id_lvl4 is null and id_lvl5 is null and
id_lvl6 is null and id_lvl7 is null and
id_lvl8 is null and id_lvl9 is null) or
(level = 4 and /* loc_id = id_lvl4 and */
id_lvl1 is not null and id_lvl2 is not null and
id_lvl3 is not null and id_lvl5 is null and
id_lvl6 is null and id_lvl7 is null and
id_lvl8 is null and id_lvl9 is null) or
(level = 5 and /* loc_id = id_lvl5 and */
id_lvl1 is not null and id_lvl2 is not null and
id_lvl3 is not null and id_lvl4 is not null and
id_lvl6 is null and id_lvl7 is null and
id_lvl8 is null and id_lvl9 is null) or
(level = 6 and /* loc_id = id_lvl6 and */
id_lvl1 is not null and id_lvl2 is not null and
id_lvl3 is not null and id_lvl4 is not null and
id_lvl5 is not null and id_lvl7 is null and
id_lvl8 is null and id_lvl9 is null) or
(level = 7 and /* loc_id = id_lvl7 and */
id_lvl1 is not null and id_lvl2 is not null and
id_lvl3 is not null and id_lvl4 is not null and
id_lvl5 is not null and id_lvl6 is not null and
id_lvl8 is null and id_lvl9 is null) or
(level = 8 and /* loc_id = id_lvl8 and */
id_lvl1 is not null and id_lvl2 is not null and
id_lvl3 is not null and id_lvl4 is not null and
id_lvl5 is not null and id_lvl6 is not null and
id_lvl7 is not null and id_lvl9 is null) or
(level = 9 and /* loc_id = id_lvl9 and */
id_lvl1 is not null and id_lvl2 is not null and
id_lvl3 is not null and id_lvl4 is not null and
id_lvl5 is not null and id_lvl6 is not null and
id_lvl7 is not null and id_lvl8 is not null)
) and
(
(valid_since is null and date_type_since is null) or
(valid_since is not null and date_type_since is not null)
)
)
);
geodb_hierarchies contains the hierarchical (== political) structures,
the location is bound to. For this we have nine attributes named
id_lvl1 to id_lvl9 containing the loc_ids of all nine political
levels. See for example the town of Karwitz (Germany) with the
loc_id 27431:
104 105 116 176 351 19122 27431 null null
id_lvl1 (104): Europe
id_lvl2 (105): Germany
id_lvl3 (116): Lower Saxony
id_lvl4 (176): Regierungsbezirk Lüneburg
id_lvl5 (351): Landkreis Lüchow-Dannenberg
id_lvl6 (19122): Samtgemeinde Dannenberg (Elbe)
id_lvl7 (27431): Karwitz
id_lvl8 (null): -
id_lvl9 (null): -
geodb_coordinates, geodb_textdata, geodb_floatdata, geodb_intdata:
create table geodb_coordinates (
loc_id integer not null references geodb_locations,
lon double precision,
lat double precision,
sin_lon double precision,
sin_lat double precision,
cos_lon double precision,
cos_lat double precision,
coord_type integer not null check (coord_type=WGS84),
coord_subtype integer,
valid_since date,
date_type_since integer,
valid_until date not null,
date_type_until integer not null
);
create table geodb_textdata (
loc_id integer not null references geodb_locations,
text_val varchar(255) not null, /* varchar(2000)? */
text_type integer not null,
text_locale varchar(5), /* ISO 639-1 */
is_native_lang smallint(1),
is_default_name smallint(1),
valid_since date,
date_type_since integer,
valid_until date not null,
date_type_until integer not null,
check (
(
(
(text_type = NAME or text_type = NAME_7BITLC or
text_type = NAME_VG or text_type = NAME_VG_7BITLC or
text_type = SOURCE or text_type = COMMENT
) and
text_locale like '__%' and
is_native_lang is not null and
is_default_name is not null
) or
(
(text_type = ISO_3166_1_ALPHA_2 or text_type = ISO_3166_2 or
text_type = AREA_CODE or text_type = CAR_LICENSE_CODE or
text_type = CAR_LIC_CODE_NAME
) and
text_locale is null and
is_native_lang is null and
is_default_name is null
)
) and
(
(valid_since is null and date_type_since is null) or
(valid_since is not null and date_type_since is not null)
)
)
);
create table geodb_intdata (
loc_id integer not null references geodb_locations,
int_val bigint not null,
int_type integer not null,
int_subtype integer,
valid_since date,
date_type_since integer,
valid_until date not null,
date_type_until integer not null
);
create table geodb_floatdata (
loc_id integer not null references geodb_locations,
float_val double precision not null,
float_type integer not null,
float_subtype integer,
valid_since date,
date_type_since integer,
valid_until date not null,
date_type_until integer not null
);
In these tables you will find any data related to the location,
maybe the name or the population or whatever may be available.
While geodb_coordinates basically just contains the longitude and
latitude values of the locations, the three following tables are
"universal containers" being able to be stuffed with any data
fitting into the related format (text / floating point
numbers / integers).
The real content is determined by the type attribute
in the table, which is text_type, float_type
or int_type.
If you like to read the name of a location, you will need a query
like:
SELECT text_val
FROM geodb_textdata
WHERE text_type=500100000 /* NAME */ AND
loc_id=27431;
- name
- name in 7 bit ASCII and small caps (e.g. for sorting)
- name of an administrative cooperation in Germany
- same thing, but again 7 bit etc.
- ISO codes for countries and provinces
- area codes
- car license codes
- official code string for communities in Germany (AGS =
Amtlicher Gemeindeschlüssel)
- source of data (only tests so far)
- comments (only tests so far)
geodb_floatdata does not yet contain any data, one could think of
the size of the area of a location or else.
geodb_intdata just contains some test data, in this case, we have
some population data of maybe 10 to 50 data sets. Other data like
the altitude of a location are possible.
geodb_type_names:
create table geodb_type_names (
type_id integer not null,
type_locale varchar(5) not null,
name varchar(255) not null,
unique (type_id, type_locale)
);
The table is supposed to contain names for the text_type, float_type
etc. attributes in geodb_textdata etc.. It not yet quite clear, if
it is really meaningful to have such information in the database.
Currently, we just have some (incomplete) data in it.
geodb_areas, geodb_polygons:
These tables do not contain any data so far. Probably the layout
of these tables will change a little or a little more, before we
include our first data. The meaning of these tables is to store
vector data in them. The most wanted data like this are currently
the border lines of states and provinces, but it is not limited
to anything.
